﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Security.Cryptography;
using System.Runtime.InteropServices;
using System.IO;
using System.Text.RegularExpressions;
using System.Windows.Controls;
using System.Reflection;
using System.Windows.Media;
//更改了项目的框架从4.5改成4.5.2是为了安装 Interactive Data Display
namespace ELM
{
    //资金管理类
    class CapitalManagement
    {
        //查询资金管理五个视图所需数据并修改列名并显示在datagrid上  刷新在执行方法就行
        public static void CapitalSelect(MainWindow window)
        {
            string sqlcommand = String.Format("select * from Capital");
            string sqlcommand1 = String.Format("select * from NoReceivables");
            string sqlcommand2 = String.Format("select * from NoPay");
            string sqlcommand3 = String.Format("select * from HadPay");
            string sqlcommand4 = String.Format("select * from HadReceivables");
            string sqlcommand5 = String.Format("select top 10 Goods_Id,Goods_Name,Warehouse_Id,Date_Of_Manufacture,Shelf_Life from Goods");
            DataTable s = DataAccess.GetResult(sqlcommand);
            DataTable s1 = DataAccess.GetResult(sqlcommand1);
            DataTable s2 = DataAccess.GetResult(sqlcommand2);
            DataTable s3 = DataAccess.GetResult(sqlcommand3);
            DataTable s4 = DataAccess.GetResult(sqlcommand4);
            DataTable s5 = DataAccess.GetResult(sqlcommand5);

            s.Columns[0].ColumnName = "编号";
            s.Columns[1].ColumnName = "货币类型";
            s.Columns[2].ColumnName = "总额";

            s1.Columns[0].ColumnName = "编号";
            s1.Columns[1].ColumnName = "金额";
            s1.Columns[2].ColumnName = "货币种类";
            s1.Columns[3].ColumnName = "甲方";
            s1.Columns[4].ColumnName = "约定日期";

            s2.Columns[0].ColumnName = "编号";
            s2.Columns[1].ColumnName = "金额";
            s2.Columns[2].ColumnName = "货币种类";
            s2.Columns[3].ColumnName = "乙方";
            s2.Columns[4].ColumnName = "约定日期";

            s3.Columns[0].ColumnName = "编号";
            s3.Columns[1].ColumnName = "金额";
            s3.Columns[2].ColumnName = "货币种类";
            s3.Columns[3].ColumnName = "甲方";
            s3.Columns[4].ColumnName = "支出日期";

            s4.Columns[0].ColumnName = "编号";
            s4.Columns[1].ColumnName = "金额";
            s4.Columns[2].ColumnName = "货币种类";
            s4.Columns[3].ColumnName = "乙方";
            s4.Columns[4].ColumnName = "收款日期";

            s5.Columns[0].ColumnName = "编号";
            s5.Columns[1].ColumnName = "名字";
            s5.Columns[2].ColumnName = "仓库";
            s5.Columns[3].ColumnName = "生产日期";
            s5.Columns[4].ColumnName = "保质期";

            window.DataShow_Capital.ItemsSource = s.DefaultView;
            window.DataShow_NoReceivable.ItemsSource = s1.DefaultView;
            window.DataShow_NoPay.ItemsSource = s2.DefaultView;
            window.DataShow_HadPay.ItemsSource = s3.DefaultView;
            window.DataShow_HadReceivable.ItemsSource = s4.DefaultView;
            window.Report_DataShow.ItemsSource = s5.DefaultView;//5是后来加的用来模拟出库单报表
        }
        // 资金汇总条件查询
        public static DataTable Capital_SelectByCondition(MainWindow window)
        {
            DataTable s = new DataTable();
            string tapOne_Currency_Type = window.TapOne_Currency_Type.Text;
            string tapOne_Total_Start = window.TapOne_Total_Start.Text;
            string tapOne_Total_End = window.TapOne_Total_End.Text;
            //Stock>=0永真条件只是让后面动态加语法成立
            string sqlcommand = String.Format("select * from Capital where 1=1");
            //至少有一个条件
            if (tapOne_Currency_Type != "" || (tapOne_Total_Start != ""&& tapOne_Total_End!=""))
            {

                if (tapOne_Currency_Type != "")
                {
                    string tapOne_Currency_Type_Condition = String.Format("and Currency_Type='{0}'", tapOne_Currency_Type);
                    sqlcommand += tapOne_Currency_Type_Condition;

                }
                if (tapOne_Total_Start != "" && tapOne_Total_End != "")
                {
                    string tapOne_Tota_Condition = String.Format("and Total>={0} and Total<={1}", tapOne_Total_Start, tapOne_Total_End);
                    sqlcommand += tapOne_Tota_Condition;
                }
            }
            else
            {
                MessageBox.Show("请至少输入一对数据");
            }
            s = DataAccess.GetResult(sqlcommand);
            s.Columns[0].ColumnName = "编号";
            s.Columns[1].ColumnName = "货币类型";
            s.Columns[2].ColumnName = "总额";
            return s;
        }
        //未收款项条件查询
        public static DataTable NoReceivables_SelectByCondition(MainWindow window)
        {
            DataTable s = new DataTable();
            string tapTwo_Currency_Type = window.TapTwo_Currency_Type.Text;
            string tapTwo_PartyA = window.TapTwo_PartyA.Text;
            string tapTwo_Fun_Start = window.TapTwo_Fun_Start.Text;
            string tapTwo_Fun_End = window.TapTwo_Fun_End.Text;
            string tapTwo_AppointedDate_Start = window.TapTwo_AppointedDate_Start.Text;
            string tapTwo_AppointedDate_End = window.TapTwo_AppointedDate_End.Text;
            //1=1永真条件只是让后面动态加语法成立
            string sqlcommand = String.Format("select * from NoReceivables where 1=1");
            //至少有一个条件
            if (tapTwo_Currency_Type != "" || tapTwo_PartyA != "" || (tapTwo_Fun_Start != "" && tapTwo_Fun_End != "") || (tapTwo_AppointedDate_Start != "" && tapTwo_AppointedDate_End != ""))
            {
                if (tapTwo_Currency_Type != "")
                {
                    string tapTwo_Currency_Type_Condition = String.Format("and Currency_Type='{0}'", tapTwo_Currency_Type);
                    sqlcommand += tapTwo_Currency_Type_Condition;
                }
                if (tapTwo_PartyA != "")
                {
                    string tapTwo_PartyA_Condition = String.Format("and PartyA='{0}'", tapTwo_PartyA);
                    sqlcommand += tapTwo_PartyA_Condition;
                }
                if (tapTwo_Fun_Start != "" && tapTwo_Fun_End != "")
                {
                    string tapTwo_Fun_Condition = String.Format(" and Fun>='{0}' and Fun<='{1}'", tapTwo_Fun_Start, tapTwo_Fun_End);
                    sqlcommand += tapTwo_Fun_Condition;
                }
                if (tapTwo_AppointedDate_Start != "" && tapTwo_AppointedDate_End != "")
                {
                    string tapTwo_AppointedDate_Condition = String.Format(" and AppointedDate>='{0}' and AppointedDate<='{1}'", tapTwo_AppointedDate_Start, tapTwo_AppointedDate_End);
                    sqlcommand += tapTwo_AppointedDate_Condition;
                }
            }
            else
            {
                MessageBox.Show("请至少输入一对数据");
            }
            s = DataAccess.GetResult(sqlcommand);
            s.Columns[0].ColumnName = "编号";
            s.Columns[1].ColumnName = "金额";
            s.Columns[2].ColumnName = "货币种类";
            s.Columns[3].ColumnName = "甲方";
            s.Columns[4].ColumnName = "约定日期";
            return s;
        }
        //未支款项条件查询
        public static DataTable NoPay_SelectByCondition(MainWindow window)
        {
            DataTable s = new DataTable();
            string tapThree_Currency_Type = window.TapThree_Currency_Type.Text;
            string tapThree_PartyB = window.TapThree_PartyB.Text;
            string tapThree_Fun_Start = window.TapThree_Fun_Start.Text;
            string tapThree_Fun_End = window.TapThree_Fun_End.Text;
            string tapThree_AppointedDate_Start = window.TapThree_AppointedDate_Start.Text;
            string tapThree_AppointedDate_End = window.TapThree_AppointedDate_End.Text;
            //1=1永真条件只是让后面动态加语法成立
            string sqlcommand = String.Format("select * from NoPay where 1=1");
            //至少有一个条件
            if (tapThree_Currency_Type != "" || tapThree_PartyB != "" || (tapThree_Fun_Start != "" && tapThree_Fun_End != "") || (tapThree_AppointedDate_Start != "" && tapThree_AppointedDate_End != ""))
            {
                if (tapThree_Currency_Type != "")
                {
                    string tapThree_Currency_Type_Condition = String.Format("and Currency_Type='{0}'", tapThree_Currency_Type);
                    sqlcommand += tapThree_Currency_Type_Condition;
                }
                if (tapThree_PartyB != "")
                {
                    string tapThree_PartyB_Condition = String.Format("and PartyB='{0}'", tapThree_PartyB);
                    sqlcommand += tapThree_PartyB_Condition;
                }
                if (tapThree_Fun_Start != "" && tapThree_Fun_End != "")
                {
                    string tapThree_Fun_Condition = String.Format(" and Fun>='{0}' and Fun<='{1}'", tapThree_Fun_Start, tapThree_Fun_End);
                    sqlcommand += tapThree_Fun_Condition;
                }
                if (tapThree_AppointedDate_Start != "" && tapThree_AppointedDate_End != "")
                {
                    string tapThree_AppointedDate_Condition = String.Format(" and AppointedDate>='{0}' and AppointedDate<='{1}'", tapThree_AppointedDate_Start, tapThree_AppointedDate_End);
                    sqlcommand += tapThree_AppointedDate_Condition;
                }
            }
            else
            {
                MessageBox.Show("请至少输入一对数据");
            }
            s = DataAccess.GetResult(sqlcommand);
            s.Columns[0].ColumnName = "编号";
            s.Columns[1].ColumnName = "金额";
            s.Columns[2].ColumnName = "货币种类";
            s.Columns[3].ColumnName = "乙方";
            s.Columns[4].ColumnName = "约定日期";
            return s;
        }
        //已支款项条件查询
        public static DataTable HadPay_SelectByCondition(MainWindow window)
        {
            DataTable s = new DataTable();
            string tapFour_Currency_Type = window.TapFour_Currency_Type.Text;
            string tapFour_PartyA = window.TapFour_PartyA.Text;
            string tapFour_Fun_Start = window.TapFour_Fun_Start.Text;
            string tapFour_Fun_End = window.TapFour_Fun_End.Text;
            string tapFour_PayDate_Start = window.TapFour_PayDate_Start.Text;
            string tapFour_PayDate_End = window.TapFour_PayDate_End.Text;
            //1=1永真条件只是让后面动态加语法成立
            string sqlcommand = String.Format("select * from HadPay where 1=1");
            //至少有一个条件
            if (tapFour_Currency_Type != "" || tapFour_PartyA != "" || (tapFour_Fun_Start != "" && tapFour_Fun_End != "") || (tapFour_PayDate_Start != "" && tapFour_PayDate_End != ""))
            {
                if (tapFour_Currency_Type != "")
                {
                    string tapFour_Currency_Type_Condition = String.Format("and Currency_Type='{0}'", tapFour_Currency_Type);
                    sqlcommand += tapFour_Currency_Type_Condition;
                }
                if (tapFour_PartyA != "")
                {
                    string tapFour_PartyA_Condition = String.Format("and PartyA='{0}'", tapFour_PartyA);
                    sqlcommand += tapFour_PartyA_Condition;
                }
                if (tapFour_Fun_Start != "" && tapFour_Fun_End != "")
                {
                    string tapFour_Fun_Condition = String.Format(" and Fun>='{0}' and Fun<='{1}'", tapFour_Fun_Start, tapFour_Fun_End);
                    sqlcommand += tapFour_Fun_Condition;
                }
                if (tapFour_PayDate_Start != "" && tapFour_PayDate_End != "")
                {
                    string tapFour_PayDate_Condition = String.Format(" and PayDate>='{0}' and PayDate<='{1}'", tapFour_PayDate_Start, tapFour_PayDate_End);
                    sqlcommand += tapFour_PayDate_Condition;
                }
            }
            else
            {
                MessageBox.Show("请至少输入一对数据");
            }
            s = DataAccess.GetResult(sqlcommand);
            s.Columns[0].ColumnName = "编号";
            s.Columns[1].ColumnName = "金额";
            s.Columns[2].ColumnName = "货币种类";
            s.Columns[3].ColumnName = "甲方";
            s.Columns[4].ColumnName = "支付日期";
            return s;
        }
        //已收款项条件查询
        public static DataTable HadReceivables_SelectByCondition(MainWindow window)
        {
            DataTable s = new DataTable();
            string tapFive_Currency_Type = window.TapFive_Currency_Type.Text;
            string tapFive_PartyB = window.TapFive_PartyB.Text;
            string tapFive_Fun_Start = window.TapFive_Fun_Start.Text;
            string tapFive_Fun_End = window.TapFive_Fun_End.Text;
            string tapFive_PaymentDate_Start = window.TapFive_PaymentDate_Start.Text;
            string tapFive_PaymentDate_End = window.TapFive_PaymentDate_End.Text;
            //1=1永真条件只是让后面动态加语法成立
            string sqlcommand = String.Format("select * from HadReceivables where 1=1");
            //至少有一个条件
            if (tapFive_Currency_Type != "" || tapFive_PartyB != "" || (tapFive_Fun_Start != "" && tapFive_Fun_End != "") || (tapFive_PaymentDate_Start != "" && tapFive_PaymentDate_End != ""))
            {
                if (tapFive_Currency_Type != "")
                {
                    string tapFive_Currency_Type_Condition = String.Format("and Currency_Type='{0}'", tapFive_Currency_Type);
                    sqlcommand += tapFive_Currency_Type_Condition;
                }
                if (tapFive_PartyB != "")
                {
                    string tapFive_PartyB_Condition = String.Format("and PartyB='{0}'", tapFive_PartyB);
                    sqlcommand += tapFive_PartyB_Condition;
                }
                if (tapFive_Fun_Start != "" && tapFive_Fun_End != "")
                {
                    string tapFive_Fun_Condition = String.Format(" and Fun>='{0}' and Fun<='{1}'", tapFive_Fun_Start, tapFive_Fun_End);
                    sqlcommand += tapFive_Fun_Condition;
                }
                if (tapFive_PaymentDate_Start != "" && tapFive_PaymentDate_End != "")
                {
                    string tapFive_PaymentDate_Condition = String.Format(" and PaymentDate>='{0}' and PaymentDate<='{1}'", tapFive_PaymentDate_Start, tapFive_PaymentDate_End);
                    sqlcommand += tapFive_PaymentDate_Condition;
                }
            }
            else
            {
                MessageBox.Show("请至少输入一对数据");
            }
            s = DataAccess.GetResult(sqlcommand);
            s.Columns[0].ColumnName = "编号";
            s.Columns[1].ColumnName = "金额";
            s.Columns[2].ColumnName = "货币种类";
            s.Columns[3].ColumnName = "乙方";
            s.Columns[4].ColumnName = "收款日期";
            return s;
        }
    }
    //客户管理类
    class CustomerManagement
    {
        //显示首页 查询客户并修改列名并显示在datagrid上  刷新在执行方法就行
        public static void CustomerSelect(MainWindow window)
        {
            string sqlcommand = String.Format("select * from Customer");
            DataTable s = DataAccess.GetResult(sqlcommand);
            s.Columns[0].ColumnName = "客户号";
            s.Columns[1].ColumnName = "客户姓名";
            s.Columns[2].ColumnName = "客户性别";
            s.Columns[3].ColumnName = "联系方式";
            s.Columns[4].ColumnName = "业务员";
            window.SDataShow_Customer.ItemsSource = s.DefaultView;
            window.UDataShow_Customer.ItemsSource = s.DefaultView;
            window.IDataShow_Customer.ItemsSource = s.DefaultView;
        }
        //客户准确查询
        public static DataTable SelectExactly(MainWindow window)
        {
            DataTable s = new DataTable();
            string sqlcommand;
            //客户号存在
            if (window.SCustomerId.Text != "")
            {
                sqlcommand = String.Format("select * from Customer where Customer_Id='{0}'", window.SCustomerId.Text);
            }
            else
            {
                sqlcommand = String.Format("select * from Customer where Tel='{0}'", window.SCustomerTel.Text);
            }
            s = DataAccess.GetResult(sqlcommand);
            s.Columns[0].ColumnName = "客户号";
            s.Columns[1].ColumnName = "客户姓名";
            s.Columns[2].ColumnName = "客户性别";
            s.Columns[3].ColumnName = "联系方式";
            s.Columns[4].ColumnName = "业务员";
            return s;
        }
        //客户条件查询
        public static DataTable SelectByCondition(MainWindow window)
        {
            DataTable s = new DataTable();
            string sex = window.Customer_Sex.Text;
            string employeeId = window.Employee_Id.Text;
            //Stock>=0永真条件只是让后面动态加语法成立
            string sqlcommand = String.Format("select * from Customer where 1=1");
            //至少有一个条件
            if (sex != "" || employeeId != "")
            {

                if (sex != "")
                {
                    string sex_Condition = String.Format("and Sex='{0}'", sex);
                    sqlcommand += sex_Condition;

                }
                if (employeeId != "")
                {
                    string sex_Condition = String.Format("and Employee_Id='{0}'", employeeId);
                    sqlcommand += sex_Condition;
                }
            }
            else
            {
                MessageBox.Show("请至少输入一对数据");
            }
            s = DataAccess.GetResult(sqlcommand);
            s.Columns[0].ColumnName = "客户号";
            s.Columns[1].ColumnName = "客户姓名";
            s.Columns[2].ColumnName = "客户性别";
            s.Columns[3].ColumnName = "联系方式";
            s.Columns[4].ColumnName = "业务员";
            return s;
        }
        //客户信息修改
        public static DataTable Customer_Update(MainWindow window)
        {
            DataTable s = new DataTable();
            string uCustomerId = window.UCustomerId.Text;
            string uCustomerName = window.UCustomerName.Text;
            string uCustomerSex = window.UCustomerSex.Text;
            string uCustomerTel = window.UCustomerTel.Text;
            string uEmployee_Id = window.UEmployee_Id.Text;
            string sqlcommand1 = String.Format("select count(*) from Customer where Customer_Id='{0}'", uCustomerId);
            string sqlcommand;
            //表里存在记录
            if (DataAccess.ExecuteScalar(sqlcommand1) > 0)
            {
                sqlcommand = String.Format("update Customer set Customer_Name='{0}',Sex='{1}',Tel='{2}',Employee_Id='{3}' where Customer_Id='{4}'", uCustomerName, uCustomerSex, uCustomerTel, uEmployee_Id,uCustomerId);
                s = DataAccess.GetResult(sqlcommand);
            }
            else
            {
                MessageBox.Show("记录中不存在此记录");
            }
            return s;
        }
        //客户信息添加
        public static DataTable Customer_Create(MainWindow window)
        {
            DataTable s = new DataTable();
            string iCustomerId = window.ICustomerId.Text;
            string iCustomerName = window.ICustomerName.Text;
            string iCustomerSex = window.ICustomerSex.Text;
            string iCustomerTel = window.ICustomerTel.Text;
            string iEmployee_Id = window.IEmployee_Id.Text;
            string sqlcommand1 = String.Format("select count(*) from Customer where Customer_Id='{0}'", iCustomerId);
            string sqlcommand;
            //表里存在记录
            if (DataAccess.ExecuteScalar(sqlcommand1) > 0)
            {
                MessageBox.Show("记录已存在"); 
            }
            else
            {
                sqlcommand = String.Format("insert into Customer values('{0}','{1}','{2}','{3}','{4}')", iCustomerId,iCustomerName, iCustomerSex, iCustomerTel, iEmployee_Id);
                s = DataAccess.GetResult(sqlcommand);
            }
            return s;
        }

    }
    //交易记录管理类
    class TransactionRecordManagement
    {
        //显示首页 查询交易记录并修改列名并显示在datagrid上  刷新在执行方法就行
        public static void TransactionRecordSelect(MainWindow window)
        {
            string sqlcommand = String.Format("select * from TransactionRecord");
            DataTable s = DataAccess.GetResult(sqlcommand);
            s.Columns[0].ColumnName = "记录号";
            s.Columns[1].ColumnName = "正式订单号";
            s.Columns[2].ColumnName = "交易创建时间";
            s.Columns[3].ColumnName = "交易结束时间";
            s.Columns[4].ColumnName = "支付状态";
            s.Columns[5].ColumnName = "支付金额";
            window.DataShow_TransactionRecord.ItemsSource = s.DefaultView;
        }
        //交易记录准确查询
        public static DataTable SelectExactly(MainWindow window)
        {
            DataTable s = new DataTable();
            string sqlcommand;
            //交易记录存在
            if (window.SRecord.Text != "")
            {
                sqlcommand = String.Format("select * from TransactionRecord where Record='{0}'", window.SRecord.Text);
            }
            else
            {
                sqlcommand = String.Format("select * from TransactionRecord where Formal_Order_Id='{0}'", window.SFormal_Order_Id.Text);
            }
            s = DataAccess.GetResult(sqlcommand);
            s.Columns[0].ColumnName = "记录号";
            s.Columns[1].ColumnName = "正式订单号";
            s.Columns[2].ColumnName = "交易创建时间";
            s.Columns[3].ColumnName = "交易结束时间";
            s.Columns[4].ColumnName = "支付状态";
            s.Columns[5].ColumnName = "支付金额";
            return s;
        }
        //交易记录条件查询
        public static DataTable SelectByCondition(MainWindow window)
        {
            DataTable s = new DataTable();
            string pay_State = window.Pay_State.Text;
            string paymentAmount_Start = window.PaymentAmount_Start.Text;
            string paymentAmount_End = window.PaymentAmount_End.Text;
            string creation_DateTime_Start = window.Creation_DateTime_Start.Text;
            string creation_DateTime_End = window.Creation_DateTime_End.Text;
            string over_DateTime_Start = window.Over_DateTime_Start.Text;
            string over_DateTime_End = window.Over_DateTime_End.Text;
            //1=1永真条件只是让后面动态加语法成立
            string sqlcommand = String.Format("select * from TransactionRecord where 1=1");
            //至少有一个条件
            if (pay_State != "" || (paymentAmount_Start != "" && paymentAmount_End != "") || (creation_DateTime_Start != "" && creation_DateTime_End != "") || (over_DateTime_Start != "" && over_DateTime_End != ""))
            {

                if (pay_State != "")
                {
                    string pay_State_Condition = String.Format("and Pay_State='{0}'", pay_State);
                    sqlcommand += pay_State_Condition;

                }
                if (paymentAmount_Start != "" && paymentAmount_End != "")
                {
                    string paymentAmount_Condition = String.Format(" and PaymentAmount>='{0}' and PaymentAmount<='{1}'", paymentAmount_Start, paymentAmount_End);
                    sqlcommand += paymentAmount_Condition;
                }
                if (creation_DateTime_Start != "" && creation_DateTime_End != "")
                {
                    string creation_DateTime_Condition = String.Format(" and Creation_DateTime>='{0}' and Creation_DateTime<='{1}'", creation_DateTime_Start, creation_DateTime_End);
                    sqlcommand += creation_DateTime_Condition;
                }
                if (over_DateTime_Start != "" && over_DateTime_End != "")
                {
                    string over_DateTime_Condition = String.Format(" and Over_DateTime>='{0}' and Over_DateTime<='{1}'", over_DateTime_Start, over_DateTime_End);
                    sqlcommand += over_DateTime_Condition;
                }
                MessageBox.Show(sqlcommand);
            }
            else
            {
                MessageBox.Show("请至少输入一对数据");
            }
            s = DataAccess.GetResult(sqlcommand);
            s.Columns[0].ColumnName = "记录号";
            s.Columns[1].ColumnName = "订单号";
            s.Columns[2].ColumnName = "交易创建时间";
            s.Columns[3].ColumnName = "交易结束时间";
            s.Columns[4].ColumnName = "支付状态";
            s.Columns[5].ColumnName = "支付金额";
            return s;
        }
    }
    //订单数据分析类
    class OrderDataAnalysis
    {
        //数据统计数据
        public static string[] GetCountData()
        {
            string[] s = new string[10];
            //第一单
            string sql1 = "select Order_Id,Create_DateTime  from FormalOrder where Create_DateTime=(select min(Create_DateTime)from FormalOrder)";
            string fristOrderId = DataAccess.GetResult(sql1).Rows[0][0].ToString();
            string fristOrderDate = DataAccess.GetResult(sql1).Rows[0][1].ToString();
            //最后一单
            string sql2 = "select Order_Id,Create_DateTime  from FormalOrder where Create_DateTime=(select max(Create_DateTime)from FormalOrder)";
            string lastOrderId = DataAccess.GetResult(sql2).Rows[0][0].ToString();
            string lastOrderDate = DataAccess.GetResult(sql2).Rows[0][1].ToString();
            //金额最大
            string sql3 = "select Record,PaymentAmount from TransactionRecord where PaymentAmount=(select max(PaymentAmount)from  TransactionRecord)";
            string maxOrderId = DataAccess.GetResult(sql3).Rows[0][0].ToString();
            string maxOrderPay = DataAccess.GetResult(sql3).Rows[0][1].ToString();
            //金额最小
            string sql4 = "select Record,PaymentAmount from TransactionRecord where PaymentAmount=(select min(PaymentAmount)from  TransactionRecord where Pay_State!='未支付')";//统计已支付的 未支付的是0没意义
            string minOrderId = DataAccess.GetResult(sql4).Rows[0][0].ToString();
            string minOrderPay = DataAccess.GetResult(sql4).Rows[0][1].ToString();
            //累计金额 累计订单
            string sql5 = "select count(Record),SUM(PaymentAmount) from TransactionRecord";//统计不管有没有支付的 因为企业订单签合同的可以先运输后付钱
            string  countOrders= DataAccess.GetResult(sql5).Rows[0][0].ToString();
            string amountOfMoney = DataAccess.GetResult(sql5).Rows[0][1].ToString();

            s[0] = fristOrderId;
            s[1] = fristOrderDate;
            s[2] = lastOrderId;
            s[3] = lastOrderDate;
            s[4] = maxOrderId;
            s[5] = maxOrderPay;
            s[6] = minOrderId;
            s[7] = minOrderPay;
            s[8] = countOrders;
            s[9] = amountOfMoney;
            return s;
        }
            //图表数据
            public static double[] GetChartData(out double[] array)
        {
            double[] s = new double[4];
            double[] s1 = new double[4];
            //第一季度订单
            string sql1 = "select COUNT(*) from FormalOrder where Create_DateTime>'2019-1-1 23:59:59' and Create_DateTime<'2019-3-31 23:59:59'";
            int orderNumber1 = Convert.ToInt32(DataAccess.GetResult(sql1).Rows[0][0]);
            //第二季度订单
            string sql2 = "select COUNT(*) from FormalOrder where Create_DateTime>'2019-4-1 23:59:59' and Create_DateTime<'2019-6-30 23:59:59'";
            int orderNumber2 = Convert.ToInt32(DataAccess.GetResult(sql2).Rows[0][0]);
            //第三记录订单
            string sql3 = "select COUNT(*) from FormalOrder where Create_DateTime>'2019-7-1 23:59:59' and Create_DateTime<'2019-9-30 23:59:59'";
            int orderNumber3 = Convert.ToInt32(DataAccess.GetResult(sql3).Rows[0][0]);
            //第四季度订单
            string sql4 = "select COUNT(*) from FormalOrder where Create_DateTime>'2019-10-1 23:59:59' and Create_DateTime<'2019-12-31 23:59:59'";
            int orderNumber4 = Convert.ToInt32(DataAccess.GetResult(sql4).Rows[0][0]);

            s[0] = orderNumber1;
            s[1] = orderNumber2;
            s[2] = orderNumber3;
            s[3] = orderNumber4;

            array = s1;
            array[0] = 1;
            array[1] = 2;
            array[2] = 3;
            array[3] = 4;

            return s;
        }
    }
    //订单管理
    class OrderManagement
    {
        //显示首页 查询订单并修改列名并显示在datagrid上  刷新在执行方法就行
        public static void OrderSelect(MainWindow window)
        {
            string sqlcommand = String.Format("select * from FormalOrder");
            DataTable s = DataAccess.GetResult(sqlcommand);
            s.Columns[0].ColumnName = "订单编号";
            s.Columns[1].ColumnName = "订单创建时间";
            s.Columns[2].ColumnName = "客户编号";
            s.Columns[3].ColumnName = "客户姓名";
            s.Columns[4].ColumnName = "客户联系方式";
            s.Columns[5].ColumnName = "发货地";
            s.Columns[6].ColumnName = "目的地";
            s.Columns[7].ColumnName = "货物";
            s.Columns[8].ColumnName = "数量";
            s.Columns[9].ColumnName = "订单状态";
            /* DataColumn dc = new DataColumn();
             dc.ColumnName = "操作";
             s.Columns.Add(dc);*/
            window.SDataShow2.ItemsSource = s.DefaultView;
            window.IDataShow2.ItemsSource = s.DefaultView;
            window.UDataShow2.ItemsSource = s.DefaultView;
        }
        //订单准确查询
        public static DataTable SelectExactly(MainWindow window)
        {
            DataTable s = new DataTable();
            string sqlcommand;
            //订单编号存在
            if (window.SOrder_Id.Text != "")
            {
                sqlcommand = String.Format("select * from FormalOrder where Order_Id='{0}'", window.SOrder_Id.Text);
            }
            else
            {//当数据库中设置格式为varchar是 查询使用的对应参数为整数时 数据库把对应字段所有数据转成对应格式整数（长度）然后与该参数对比 所以当该该字段存在比该参数长的数据是就会溢出
            //                如：select* from FormalOrder where Customer_Id = 2188140
            //报错：转换 varchar 值 ‘17705053661’ 时溢出了整数列。所以建议传数据时都加上‘’
                sqlcommand = String.Format("select * from FormalOrder where Customer_Id='{0}'", window.SCustomer_Id.Text);
            }
            s = DataAccess.GetResult(sqlcommand);
            s.Columns[0].ColumnName = "订单编号";
            s.Columns[1].ColumnName = "订单创建时间";
            s.Columns[2].ColumnName = "客户编号";
            s.Columns[3].ColumnName = "客户姓名";
            s.Columns[4].ColumnName = "客户联系方式";
            s.Columns[5].ColumnName = "发货地";
            s.Columns[6].ColumnName = "目的地";
            s.Columns[7].ColumnName = "货物";
            s.Columns[8].ColumnName = "数量";
            s.Columns[9].ColumnName = "订单状态";
            return s;
        }
        //订单条件查询
        public static DataTable SelectByCondition(MainWindow window)
        {
            DataTable s = new DataTable();
            string place_Of_Delivery = window.Place_Of_Delivery.Text;
            string destination = window.Destination.Text;
            string goods = window.Goods.Text;
            string order_State = window.Order_State.Text;
            string create_DateTime_Start = window.Create_DateTime_Start.Text;
            string create_DateTime_End = window.Create_DateTime_End.Text;
            //1=1永真条件只是让后面动态加语法成立
            string sqlcommand = String.Format("select * from FormalOrder where 1=1");
            //至少有一个条件
            if (place_Of_Delivery != "" || destination != ""|| goods!=""|| order_State!=""|| (create_DateTime_Start != ""&& create_DateTime_End != ""))
            {

                if (place_Of_Delivery != "")
                {
                    string place_Of_Delivery_Condition = String.Format("and Place_Of_Delivery='{0}'", place_Of_Delivery);
                    sqlcommand += place_Of_Delivery_Condition;

                }
                if (destination != "")
                {
                    string destination_Condition = String.Format("and Destination='{0}'", destination);
                    sqlcommand += destination_Condition;
                }
                if (goods != "")
                {
                    string goods_Condition = String.Format("and Goods='{0}'", goods);
                    sqlcommand += goods_Condition;
                }
                if (order_State != "")
                {
                    string order_State_Condition = String.Format("and Order_State='{0}'", order_State);
                    sqlcommand += order_State_Condition;
                }
                if (create_DateTime_Start != "" && create_DateTime_End != "")
                {
                    string create_DateTime_Condition = String.Format(" and Create_DateTime>='{0}' and Create_DateTime<='{1}'", create_DateTime_Start, create_DateTime_End);
                    sqlcommand += create_DateTime_Condition;
                }
            }
            else
            {
                MessageBox.Show("请至少输入一对数据");
            }
            s = DataAccess.GetResult(sqlcommand);
            s.Columns[0].ColumnName = "订单编号";
            s.Columns[1].ColumnName = "创建时间";
            s.Columns[2].ColumnName = "客户编号";
            s.Columns[3].ColumnName = "客户名";
            s.Columns[4].ColumnName = "客户联系方式";
            s.Columns[5].ColumnName = "发货地";
            s.Columns[6].ColumnName = "目的地";
            s.Columns[7].ColumnName = "货物";
            s.Columns[8].ColumnName = "数量";
            s.Columns[9].ColumnName = "订单状态";
            return s;
        }
        //订单插入
        public static DataTable Order_Insert(MainWindow window)
        {
            DataTable s = new DataTable();
            string iOrder_Id =window.IOrder_Id.Text;
            string iCreate_DateTime = window.ICreate_DateTime.Text;
            string iCustomer_Id = window.ICustomer_Id.Text;
            string iCustomer_Name = window.ICustomer_Name.Text;
            string iCustomer_Tel = window.ICustomer_Tel.Text;
            string iPlace_Of_Delivery = window.IPlace_Of_Delivery.Text;
            string iDestination = window.IDestination.Text;
            string iGoods = window.IGoods.Text;
            string iNumber = window.INumber.Text;
            string iOrder_State = window.IOrder_State.Text;

            string sqlcommand1 = String.Format("select count(*) from FormalOrder where Order_Id='{0}'", iOrder_Id);
            string sqlcommand;
            //表里存在记录
            if (DataAccess.ExecuteScalar(sqlcommand1) > 0)
            {
                MessageBox.Show("存在此条记录，请勿重复插入");
            }
            else
            {
                sqlcommand = String.Format("insert into FormalOrder values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}',{8},'{9}')", iOrder_Id, iCreate_DateTime, iCustomer_Id, iCustomer_Name, iCustomer_Tel,iPlace_Of_Delivery, iDestination, iGoods,iNumber,iOrder_State);
                s = DataAccess.GetResult(sqlcommand);   
            }
            return s;
        }
        //订单修改
        public static DataTable Order_Update(MainWindow window)
        {
            DataTable s = new DataTable();
            string uOrder_Id = window.UOrder_Id.Text;
            string uCreate_DateTime = window.UCreate_DateTime.Text;
            string uCustomer_Id = window.UCustomer_Id.Text;
            string uCustomer_Name = window.UCustomer_Name.Text;
            string uCustomer_Tel = window.UCustomer_Tel.Text;
            string uPlace_Of_Delivery = window.UPlace_Of_Delivery.Text;
            string uDestination = window.UDestination.Text;
            string uGoods = window.UGoods.Text;
            string uNumber = window.UNumber.Text;
            string uOrder_State = window.UOrder_State.Text;
            string sqlcommand1 = String.Format("select count(*) from FormalOrder where Order_Id='{0}'", uOrder_Id);
            string sqlcommand;
            //表里存在记录
            if (DataAccess.ExecuteScalar(sqlcommand1) > 0)
            {
                sqlcommand = String.Format("update FormalOrder set Create_DateTime='{0}',Customer_Id='{1}',Customer_Name='{2}',Customer_Tel='{3}',Place_Of_Delivery='{4}',Destination='{5}',Goods='{6}',Number={7},Order_State='{8}' where Order_Id='{9}'", uCreate_DateTime, uCustomer_Id, uCustomer_Name, uCustomer_Tel, uPlace_Of_Delivery, uDestination, uGoods, uNumber, uOrder_State,uOrder_Id);
                s = DataAccess.GetResult(sqlcommand);
            }
            else
            {
                MessageBox.Show("记录中不存在此记录");
            }
            return s;
        }
    }
    //运输工具管理
    class TransportManagement
    {
        //显示首页 查询运输工具并修改列名并显示在datagrid上  刷新在执行方法就行
        public static void TransportSelect(MainWindow window)
        {
            string sqlcommand = String.Format("select * from Conveyance");
            DataTable s = DataAccess.GetResult(sqlcommand);
            s.Columns[0].ColumnName = "工具编号";
            s.Columns[1].ColumnName = "工具种类";
            s.Columns[2].ColumnName = "驾驶员";
            s.Columns[3].ColumnName = "是否空闲";
            s.Columns[4].ColumnName = "续航";
            s.Columns[5].ColumnName = "燃料类型";
            s.Columns[6].ColumnName = "载物量";
            /* DataColumn dc = new DataColumn();
             dc.ColumnName = "操作";
             s.Columns.Add(dc);*/
            window.SDataShow1.ItemsSource = s.DefaultView;
            window.IDataShow1.ItemsSource = s.DefaultView;
            window.UDataShow1.ItemsSource = s.DefaultView;
        }
        //运输工具准确查询
        public static DataTable SelectExactly(MainWindow window)
        {
            DataTable s = new DataTable();
            string sqlcommand;
            //工具编号存在
            if (window.STool_Id.Text != "")
            {
                sqlcommand = String.Format("select * from Conveyance where Tool_Id={0}", window.STool_Id.Text);
            }
            else
            {
                sqlcommand = String.Format("select * from Conveyance where Tool_Type='{0}'", window.STool_Type.Text);
            }
            s=DataAccess.GetResult(sqlcommand);
            s.Columns[0].ColumnName = "工具编号";
            s.Columns[1].ColumnName = "工具种类";
            s.Columns[2].ColumnName = "驾驶员";
            s.Columns[3].ColumnName = "是否空闲";
            s.Columns[4].ColumnName = "续航";
            s.Columns[5].ColumnName = "燃料类型";
            s.Columns[6].ColumnName = "载物量";
            return s;
        }
        //运输工具条件查询
        public static DataTable SelectByCondition(MainWindow window)
        {
            DataTable s = new DataTable();
            string tool_Type = window.Tool_Type.Text;
            string is_Free = window.Is_Free.Text;
            string fuel_Type = window.Fuel_Type.Text;
            string endurance_Start = window.Endurance_Start.Text;
            string endurance_End = window.Endurance_End.Text;
            string deadweight_Start = window.Deadweight_Start.Text;
            string deadweight_End = window.Deadweight_End.Text;
            //1=1永真条件只是让后面动态加语法成立
            string sqlcommand = String.Format("select * from Conveyance where 1=1");
            //至少有一个条件
            if (tool_Type != "" || is_Free != "" || fuel_Type != "" || (endurance_Start != "" && endurance_End != "") || (deadweight_Start != "" && deadweight_End != ""))
            {

                if (tool_Type != "")
                {
                    string tool_Type_Condition = String.Format("and Tool_Type='{0}'", tool_Type);
                    sqlcommand += tool_Type_Condition;

                }
                if (is_Free != "")
                {
                    string is_Free_Condition = String.Format("and Is_Free='{0}'", is_Free);
                    sqlcommand += is_Free_Condition;
                }
                if (fuel_Type != "")
                {
                    string fuel_Type_Condition = String.Format("and Fuel_Type='{0}'", fuel_Type);
                    sqlcommand += fuel_Type_Condition;
                }
                if (endurance_Start != "" && endurance_End != "")
                {
                    string endurance_Condition = String.Format(" and Endurance>='{0}' and Endurance<='{1}'", endurance_Start, endurance_End);
                    sqlcommand += endurance_Condition;
                }
                if (deadweight_Start != "" && deadweight_End != "")
                {
                    string deadweight_Condition = String.Format(" and Deadweight>='{0}' and Deadweight<='{1}'", deadweight_Start, deadweight_End);
                    sqlcommand += deadweight_Condition;
                } 
            }
            else
            {
                MessageBox.Show("请至少输入一对数据");
            }
            s = DataAccess.GetResult(sqlcommand);
            s.Columns[0].ColumnName = "工具编号";
            s.Columns[1].ColumnName = "工具类型";
            s.Columns[2].ColumnName = "驾驶员";
            s.Columns[3].ColumnName = "是否空闲";
            s.Columns[4].ColumnName = "续航";
            s.Columns[5].ColumnName = "燃料类型";
            s.Columns[6].ColumnName = "载重量";
            return s;
        }
        //运输工具插入
        public static DataTable Transport_Insert(MainWindow window)
        {
            DataTable s = new DataTable();
            string iTool_Id = window.ITool_Id.Text;
            string iTool_Type = window.ITool_Type.Text;
            string iDriver = window.IDriver.Text;
            string iIs_Free = window.IIs_Free.Text;
            string iEndurance = window.IEndurance.Text;
            string iFuel_Type = window.IFuel_Type.Text;
            string iDeadweight = window.IDeadweight.Text;
            string sqlcommand1 = String.Format("select count(*) from Conveyance where Tool_Id={0}", iTool_Id);
            string sqlcommand;
            //表里存在记录
            if (DataAccess.ExecuteScalar(sqlcommand1) > 0)
            {
                MessageBox.Show("存在此条记录，请勿重复插入");
            }
            else
            {
                sqlcommand = String.Format("insert into Conveyance values({0},'{1}','{2}',{3},{4},'{5}',{6})",iTool_Id,iTool_Type,iDriver, iIs_Free,iEndurance, iFuel_Type, iDeadweight);
                s = DataAccess.GetResult(sqlcommand);
            }
            return s;            
        }
        //运输工具修改
        public static DataTable Transport_Update(MainWindow window)
        {
            DataTable s = new DataTable();
            string uTool_Id = window.UTool_Id.Text;
            string uTool_Type = window.UTool_Type.Text;
            string uDriver = window.UDriver.Text;
            string uIs_Free = window.UIs_Free.Text;
            string uEndurance = window.UEndurance.Text;
            string uFuel_Type = window.UFuel_Type.Text;
            string uDeadweight = window.UDeadweight.Text;
            string sqlcommand1 = String.Format("select count(*) from Conveyance where Tool_Id={0}", uTool_Id);
            string sqlcommand;
            //表里存在记录
            if (DataAccess.ExecuteScalar(sqlcommand1) > 0)
            {
                sqlcommand = String.Format("update Conveyance set Tool_Type='{0}',Driver='{1}',Is_Free={2},Endurance={3},Fuel_Type='{4}',Deadweight={5} where Tool_Id={6}", uTool_Type, uDriver, uIs_Free, uEndurance, uFuel_Type, uDeadweight,uTool_Id);
                s = DataAccess.GetResult(sqlcommand);
            }
            else
            {
                MessageBox.Show("记录中不存在此记录");
            }
            return s;
        }
    }
    //货物数据分析
    class DataAnalysis
    {
        //图表数据
        public static double[] GetChartData(out double[] array, out double[] array1, out double[] array2)
        {
            double[] s = new double[5];
            double[] s1= new double[5];
            double[] s2 = new double[5];
            double[] s3 = new double[5];
            //保质期剩余小于一周
            string sql1 = "select COUNT(*) from Goods where (Shelf_Life-(datediff(DAY,Date_Of_Manufacture ,getDate())))<7";
            int shelf_Life7 =Convert.ToInt32(DataAccess.GetResult(sql1).Rows[0][0]);
            //保质期剩余小于一个月
            string sql2 = "select COUNT(*) from Goods where (Shelf_Life-(datediff(DAY,Date_Of_Manufacture ,getDate())))<30";
            int shelf_Life30 = Convert.ToInt32(DataAccess.GetResult(sql2).Rows[0][0]);
            //保质期剩余小于三个月
            string sql3 = "select COUNT(*) from Goods where (Shelf_Life-(datediff(DAY,Date_Of_Manufacture ,getDate())))<90";
            int shelf_Life90 = Convert.ToInt32(DataAccess.GetResult(sql3).Rows[0][0]);
            //保质期剩余小于半年
            string sql4 = "select COUNT(*) from Goods where (Shelf_Life-(datediff(DAY,Date_Of_Manufacture ,getDate())))<180";
            int shelf_Life180 = Convert.ToInt32(DataAccess.GetResult(sql4).Rows[0][0]);
            //保质期剩余小于1年
            string sql5 = "select COUNT(*) from Goods where (Shelf_Life-(datediff(DAY,Date_Of_Manufacture ,getDate())))<360";
            int shelf_Life360 = Convert.ToInt32(DataAccess.GetResult(sql5).Rows[0][0]);

            s[0]=shelf_Life7;
            s[1] = shelf_Life30;
            s[2] = shelf_Life90;
            s[3] = shelf_Life180;
            s[4] = shelf_Life360;

            array = s1;
            array[0] = 7;
            array[1] = 30;
            array[2] = 90;
            array[3] = 180;
            array[4] = 360;

            //库存剩余小于100 
            string sql6 = "select COUNT(*) from Warehouse where Capacity_Unused < 100";
            int capacity_Unused100= Convert.ToInt32(DataAccess.GetResult(sql6).Rows[0][0]);
            //库存剩余小于500
            string sql7 = "select COUNT(*) from Warehouse where Capacity_Unused < 500";
            int capacity_Unused500 = Convert.ToInt32(DataAccess.GetResult(sql7).Rows[0][0]);
            //库存剩余小于1000
            string sql8 = "select COUNT(*) from Warehouse where Capacity_Unused < 1000";
            int capacity_Unused1000 = Convert.ToInt32(DataAccess.GetResult(sql8).Rows[0][0]);
            //库存剩余小于5000
            string sql9 = "select COUNT(*) from Warehouse where Capacity_Unused < 5000";
            int capacity_Unused5000 = Convert.ToInt32(DataAccess.GetResult(sql9).Rows[0][0]);
            //库存剩余小于10000
            string sql10 = "select COUNT(*) from Warehouse where Capacity_Unused < 10000";
            int capacity_Unused10000 = Convert.ToInt32(DataAccess.GetResult(sql10).Rows[0][0]);

            array1 = s2;
            array1[0] =capacity_Unused100;
            array1[1] = capacity_Unused500;
            array1[2] = capacity_Unused1000;
            array1[3] = capacity_Unused5000;
            array1[4] = capacity_Unused10000;

            array2 = s3;
            array2[0] = 100;
            array2[1] =500;
            array2[2] =1000;
            array2[3] = 5000;
            array2[4] =10000;

            return s;
        }

        //读取样本数据IO
        public static List<EasyGoods> GetEasyGoodsDataset(string filePath)
        {
            FileStream fs = new FileStream(filePath, System.IO.FileMode.Open);
            StreamReader sr = new StreamReader(fs,Encoding.Default);//,Encoding.Default读取是防止中文乱码
            //
            List<EasyGoods> list = new List<EasyGoods>();
            string readLine = sr.ReadLine();
            while (!string.IsNullOrEmpty(readLine))
            {
                string[] splitArray = readLine.Split(' ');
                EasyGoods easyGoods = new EasyGoods();
                easyGoods.ShelfLife = Convert.ToDouble(splitArray[1]);
                easyGoods.Stock = Convert.ToDouble(splitArray[2]);
                easyGoods.Strategy = splitArray[3];
                list.Add(easyGoods);
                readLine = sr.ReadLine();
            }
            //
            sr.Close();
            fs.Close();
            fs.Dispose();
            return list;
        }
    }
    //库存管理  查询下的上一页、下一页、跳页改了其他没动
    class StockManage
    {
        //出+入库+修改+添加
        public static DataTable Stock_O_I_C_I(MainWindow window, string goodsId,string goodsName,string warehouseId,string stock,string dateOfManufacture,string shelfLife,string number)
        {
            DataTable s = new DataTable();
            string sqlcommand;
            //查询有没有此记录sql
            string sqlcommand1 = String.Format("select count(*) from Goods where Goods_Id={0}", goodsId);
            //入库下
            if (window.StockIn.IsSelected == true)
            {
                if (goodsId != "")
                {
                    //入库
                    if (number != "")
                {
                        sqlcommand = String.Format("update Goods set Stock+={0} where Goods_Id={1}", number, goodsId);
                        DataAccess.NoResult(sqlcommand);
                       window.IDataShow.ItemsSource=RefreshSkipPage(window).DefaultView;
                }

                else
                {   //修改
            
                    if (DataAccess.ExecuteScalar(sqlcommand1) > 0)
                    {
                        sqlcommand = String.Format("update Goods set Goods_Name='{0}',Warehouse_Id={1},Stock={2},Date_Of_Manufacture='{3}',Shelf_Life={4} where Goods_Id={5}",goodsName, warehouseId, stock, dateOfManufacture, shelfLife,goodsId);
                        DataAccess.NoResult(sqlcommand);
                        window.IDataShow.ItemsSource = RefreshSkipPage(window).DefaultView;
                    }
                    //添加
                    else
                    {
                        sqlcommand = String.Format("insert into Goods values({0},'{1}',{2},{3},'{4}',{5})", goodsId, goodsName, warehouseId, stock, dateOfManufacture, shelfLife);
                        DataAccess.NoResult(sqlcommand);
                        MessageBox.Show("添加成功");
                        window.IDataShow.ItemsSource = RefreshSkipPage(window).DefaultView;
                    }
                }
                }
                else
                {
                    MessageBox.Show("货物编码必须存在");
                }
            }
            //出库下
            if (window.StockOut.IsSelected == true)
            {
                if (goodsId != "")
                {
                    //出库 
                    if (number != "")
                {
                        sqlcommand = String.Format("update Goods set Stock-={0} where Goods_Id={1}", number, goodsId);
                        DataAccess.NoResult(sqlcommand);
                        window.IDataShow.ItemsSource = RefreshSkipPage(window).DefaultView;
                }
                else
                {       //修改
                    if (DataAccess.ExecuteScalar(sqlcommand1) > 0)
                    {
                        sqlcommand = String.Format("update Goods set Goods_Name='{0}'，Warehouse_Id={1},Stock={2},Date_Of_Manufacture='{3}',Shelf_Life={4} where Goods_Id={5};", goodsName, warehouseId, stock, dateOfManufacture, shelfLife,goodsId);
                        DataAccess.NoResult(sqlcommand);
                        window.IDataShow.ItemsSource = RefreshSkipPage(window).DefaultView;
                    }
                    //添加
                    else
                    {
                        sqlcommand = String.Format("insert into Goods values({0},'{1}',{2},{3},'{4}',{5})", goodsId, goodsName, warehouseId, stock, dateOfManufacture, shelfLife);
                        DataAccess.NoResult(sqlcommand);
                        MessageBox.Show("添加成功");
                        window.IDataShow.ItemsSource = RefreshSkipPage(window).DefaultView;
                    }
                }
                }
                else
                {
                    MessageBox.Show("货物编码必须存在");
                }
            }
            return s;
        }
        //库存操作
        public static DataTable StockOperation(string goodsId, string operation)
        {
            DataTable s = new DataTable();
            //如+=1，=0，-=1
            string sqlcommand = String.Format("Update Goods set Stock{0} where Goods_Id={1}", operation, goodsId);
            DataAccess.GetResult(sqlcommand);
            return s;
        }

        //查询下的条件查询
        public static DataTable SelectByCondition(MainWindow window)
        {
            DataTable s = new DataTable();
            string SW_Start = window.SW_Start.Text;
            string SW_End = window.SW_End.Text;
            string SS_Start = window.SS_Start.Text;
            string SS_End = window.SS_End.Text;
            string SD_Start = window.SD_Start.Text;
            string SD_End = window.SD_End.Text;
            string SL_Start = window.SL_Start.Text;
            string SL_End = window.SL_End.Text;
            //Stock>=0永真条件只是让后面动态加语法成立
            string sqlcommand = String.Format("select * from Goods where Stock>=0");
            //存在一对数据
            if ((SW_Start != "" && SW_End != "") || (SS_Start != "" && SS_End != "") || (SD_Start != "" && SD_End != "") || (SL_Start != "" && SL_End != ""))
            {    
                //动态添加sql条件
                if (SW_Start!="")
                {
                    string SW_Start_Condition = String.Format("and Warehouse_Id>={0}", SW_Start);
                    sqlcommand +=SW_Start_Condition;
                }
                if (SW_End != "")
                {
                    string SW_End_Condition = String.Format("and Warehouse_Id<={0}", SW_End);
                    sqlcommand +=SW_End_Condition;
                }
                if (SS_Start != "")
                {
                    string SS_Start_Condition = String.Format("and Stock>={0}", SS_Start);
                    sqlcommand +=SS_Start_Condition;
                }
                if (SS_End != "")
                {
                    string SS_End_Condition = String.Format("and Stock<={0}", SS_End);
                    sqlcommand +=SS_End_Condition;
                }
                if (SD_Start != "")
                {
                    //不加''会报错
                    string SD_Start_Condition = String.Format("and Date_Of_Manufacture>='{0}'", SD_Start);
                    sqlcommand +=SD_Start_Condition;
                }
                if (SD_End != "")
                {
                    string SD_End_Condition = String.Format("and Date_Of_Manufacture<='{0}'", SD_End);
                    sqlcommand +=SD_End_Condition;
                }
                if (SL_Start != "")
                {
                    string SL_Start_Condition = String.Format("and Shelf_Life>={0}", SL_Start);
                    sqlcommand +=SL_Start_Condition;
                }
                if (SL_End != "")
                {
                    string SL_End_Condition = String.Format("and Shelf_Life<={0}", SL_End);
                    sqlcommand +=SL_End_Condition;
                }
            }
            else
            {
                MessageBox.Show("至少要有一对数据呦");
            }
            //MessageBox.Show(sqlcommand);
            try { s = DataAccess.GetResult(sqlcommand);}
            catch { MessageBox.Show("哪里出错了吗"); }
            s.Columns[0].ColumnName = "货物编码";
            s.Columns[1].ColumnName = "货物名";
            s.Columns[2].ColumnName = "仓库号";
            s.Columns[3].ColumnName = "库存";
            s.Columns[4].ColumnName = "生产日期";
            s.Columns[5].ColumnName = "保质期";
            return s;

        }
        //查询下准确查询
        public static DataTable SelectExactly(MainWindow window)
        {
            string goodsId = window.SGoodsId.Text;
            string goodsName = window.SGoodsName.Text;
            DataTable s = new DataTable();
            string sCondition = String.Format(" where Goods_Id='{0}'", goodsId);
            if (goodsId == "" && goodsName == "")
            {
                MessageBox.Show("二者至少有一个要有数据");
            }
            else
            {    
                if (goodsId == "")
                {
                    sCondition = String.Format(" where Goods_Name='{0}'", goodsName);
                }
                string sqlcommand = String.Format("select * from Goods {0}", sCondition);
                s = DataAccess.GetResult(sqlcommand);
            }
            s.Columns[0].ColumnName = "货物编码";
            s.Columns[1].ColumnName = "货物名";
            s.Columns[2].ColumnName = "仓库号";
            s.Columns[3].ColumnName = "库存";
            s.Columns[4].ColumnName = "生产日期";
            s.Columns[5].ColumnName = "保质期";
            return s;

        }
        //显示首页 查询货物消息并修改列名并显示在datagrid上  刷新在执行方法就行
        public static void GoodsSelect(MainWindow window)
        {
            string sqlcommand = String.Format("select * from Goods");
            DataTable s = DataAccess.GetResult(sqlcommand);
            s.Columns[0].ColumnName = "货物编码";
            s.Columns[1].ColumnName = "货物名";
            s.Columns[2].ColumnName = "仓库号";
            s.Columns[3].ColumnName = "库存";
            s.Columns[4].ColumnName = "生产日期";
            s.Columns[5].ColumnName = "保质期";
            /* DataColumn dc = new DataColumn();
             dc.ColumnName = "操作";
             s.Columns.Add(dc);*/
            window.SDataShow.ItemsSource = s.DefaultView;
            window.IDataShow.ItemsSource = s.DefaultView;
            window.ODataShow.ItemsSource = s.DefaultView;
        }
        //库存视图上一页 
        public static DataTable PreviousPage(MainWindow window)
        {    //无论成功与否都返回s      上一页 下一页 跳页当s为空时处理方式不好现在是：想办法把空s填数据避免报错 改进可以在调用后判断返回的datatable 如空则不做处理（试图保持现状）
            string sqlcommand = String.Format("select * from Goods");
            DataTable s = DataAccess.GetResult(sqlcommand);
            //如果在查询下
            if (window.Select.IsSelected==true)
            {
                if (Bridge.SPageNumber == 22)
                {
                    MessageBox.Show("已经在第一页啦");
                    }
                else
                {
                   int temp = Bridge.SPageNumber;
                    temp -= 44;
                    Bridge.SPageNumber -= 22;
                    string sqlcommand1 = String.Format("select top 22 * from  Goods where Goods_Id  not in(select top {0} Goods_Id from Goods) and Goods_Id not in(select top 22 Goods_Id from  Goods where Goods_Id  not in(select top {1} Goods_Id from Goods))", temp,Bridge.SPageNumber);
                   s=DataAccess.GetResult(sqlcommand1);
                }
            }
            //如果在入库下
            if (window.StockIn.IsSelected == true)
            {
                if (Bridge.IPageNumber == 22)
                {
                    MessageBox.Show("已经在第一页啦");
                }
                else
                {
                    Bridge.IPageNumber -= 22;
                    string sqlcommand1 = String.Format("select top 22 * from  Goods where Goods_Id  not in(select top {0} Goods_Id from Goods)", Bridge.IPageNumber);
                    s=DataAccess.GetResult(sqlcommand1);
                }
            }
            //如果在出库下
            if (window.StockOut.IsSelected == true)
            {
                if (Bridge.OPageNumber == 22)
                {
                    MessageBox.Show("已经在第一页啦");
                }
                else
                {
                    Bridge.OPageNumber -= 22;
                    string sqlcommand1 = String.Format("select top 22 * from  Goods where Goods_Id  not in(select top {0} Goods_Id from Goods)", Bridge.OPageNumber);
                   s=DataAccess.GetResult(sqlcommand1);
                }
            }
                s.Columns[0].ColumnName = "货物编码";
                s.Columns[1].ColumnName = "货物名";
                s.Columns[2].ColumnName = "仓库号";
                s.Columns[3].ColumnName = "库存";
                s.Columns[4].ColumnName = "生产日期";
                s.Columns[5].ColumnName = "保质期";           
                return s;
        }
        //库存视图下一页
        public static DataTable NextPage(MainWindow window)
        {
            string sqlcommand = String.Format("select count(*) from Goods");
            int temp = DataAccess.ExecuteScalar(sqlcommand);
            //如果在查询下
            DataTable s = new DataTable();
            if (window.Select.IsSelected == true)
            {
                if (temp - Bridge.SPageNumber<0) //(temp - Bridge.SPageNumber) % 22 == (temp - Bridge.SPageNumber)
                {
                    MessageBox.Show("已经在最后一页啦页啦");

                }
                else
                {
                    string sqlcommand1 = String.Format("select top 22 * from  Goods where Goods_Id  not in(select top {0} Goods_Id from Goods)", Bridge.SPageNumber);
                    s = DataAccess.GetResult(sqlcommand1);
                    Bridge.SPageNumber += 22;
                }
            }
            //如果在入库下
            if (window.StockIn.IsSelected == true)
            {
                if (temp - Bridge.IPageNumber < 0)
                {
                    MessageBox.Show("已经在最后一页啦页啦");

                }
                else
                {
                    Bridge.SPageNumber += 22;
                    string sqlcommand1 = String.Format("select top 22 * from  Goods where Goods_Id  not in(select top {0} Goods_Id from Goods)", Bridge.SPageNumber);
                    s = DataAccess.GetResult(sqlcommand1);
                }
            }
            //如果在出库下
            if (window.StockOut.IsSelected == true)
            {
                if (temp - Bridge.OPageNumber < 0)
                {
                    MessageBox.Show("已经在最后一页啦页啦");

                }
                else
                {
                    Bridge.SPageNumber += 22;
                    string sqlcommand1 = String.Format("select top 22 * from  Goods where Goods_Id  not in(select top {0} Goods_Id from Goods)", Bridge.SPageNumber);
                    s = DataAccess.GetResult(sqlcommand1);
                }
            }
            //捕获由在最后一页是s为空导致的错误
            try
            {
                s.Columns[0].ColumnName = "货物编码";
                s.Columns[1].ColumnName = "货物名";
                s.Columns[2].ColumnName = "仓库号";
                s.Columns[3].ColumnName = "库存";
                s.Columns[4].ColumnName = "生产日期";
                s.Columns[5].ColumnName = "保质期";
            }
            catch
            {
                string sqlcommand1 = String.Format("select top 22 * from  Goods where Goods_Id  not in(select top {0} Goods_Id from Goods)", Bridge.SPageNumber-22);
                s = DataAccess.GetResult(sqlcommand1);
                s.Columns[0].ColumnName = "货物编码";
                s.Columns[1].ColumnName = "货物名";
                s.Columns[2].ColumnName = "仓库号";
                s.Columns[3].ColumnName = "库存";
                s.Columns[4].ColumnName = "生产日期";
                s.Columns[5].ColumnName = "保质期";
            }
            return s;
        }
        //库存试图跳页  TMD应该现在跳页然后上一页下一页都调用跳页根据页数来做  思想出了问题
        public static DataTable SkipPage(MainWindow window)
        {
            DataTable s = new DataTable();
            //如果在查询下
            //没有输页数
                if (window.Select.IsSelected == true)
           {
                if (window.SPageNumber.Text == "")
                {
                    MessageBox.Show("请输入页数");
                }
                else
                {
                    if (Bridge.PageCount<int.Parse(window.SPageNumber.Text))
                    {
                        MessageBox.Show("此页不存在");
                    }
                    else
                    {
                        //算出大范围
                        int bTopNumber = int.Parse(window.SPageNumber.Text) * 22;
                        //算出小范围
                        int lTopNumber = (int.Parse(window.SPageNumber.Text) - 1) * 22;
                        string sqlcommand1 = String.Format("select top {0} * from  Goods where Goods_Id  not in(select top {1} Goods_Id from Goods)", bTopNumber, lTopNumber);
                        s = DataAccess.GetResult(sqlcommand1);
                    }                    
                }
            }
            //如果在入库下
            //没有输页数
                if (window.StockIn.IsSelected == true)
            {
                if (window.IPageNumber.Text == "")
                {
                    MessageBox.Show("请输入页数");
                }
                else
                {
                    if (Bridge.PageCount < int.Parse(window.SPageNumber.Text))
                    {
                        MessageBox.Show("此页不存在");
                    }
                    else
                    {
                        //算出大范围
                        int bTopNumber = int.Parse(window.IPageNumber.Text) * 22;
                        //算出小范围
                        int lTopNumber = (int.Parse(window.IPageNumber.Text) - 1) * 22;
                        string sqlcommand1 = String.Format("select top {0} * from  Goods where Goods_Id  not in(select top {1} Goods_Id from Goods)", bTopNumber, lTopNumber);
                        s = DataAccess.GetResult(sqlcommand1);
                    }
                }
            }
            //如果在出库下
            //没有输页数
                if (window.StockOut.IsSelected == true)
           {
                if (window.OPageNumber.Text == "")
                {
                    MessageBox.Show("请输入页数");
                }
                else
                {
                    if (Bridge.PageCount < int.Parse(window.SPageNumber.Text))
                    {
                        MessageBox.Show("此页不存在");
                    }
                    else
                    {
                        //算出大范围
                        int bTopNumber = int.Parse(window.OPageNumber.Text) * 22;
                        //算出小范围
                        int lTopNumber = (int.Parse(window.OPageNumber.Text) - 1) * 22;
                        string sqlcommand1 = String.Format("select top {0} * from  Goods where Goods_Id  not in(select top {1} Goods_Id from Goods)", bTopNumber, lTopNumber);
                        s = DataAccess.GetResult(sqlcommand1);
                    }
                }
            }
            try
            {
                s.Columns[0].ColumnName = "货物编码";
                s.Columns[1].ColumnName = "货物名";
                s.Columns[2].ColumnName = "仓库号";
                s.Columns[3].ColumnName = "库存";
                s.Columns[4].ColumnName = "生产日期";
                s.Columns[5].ColumnName = "保质期";
            }
            catch(Exception e)
            {
                string sqlcommand1 = String.Format("select top 22 * from  Goods where Goods_Id  not in(select top {0} Goods_Id from Goods)", Bridge.SPageNumber - 22);
                s = DataAccess.GetResult(sqlcommand1);
                s.Columns[0].ColumnName = "货物编码";
                s.Columns[1].ColumnName = "货物名";
                s.Columns[2].ColumnName = "仓库号";
                s.Columns[3].ColumnName = "库存";
                s.Columns[4].ColumnName = "生产日期";
                s.Columns[5].ColumnName = "保质期";
                Console.WriteLine(e.Message);
            }
            return s;
        }
        //刷新跳页
        public static DataTable RefreshSkipPage(MainWindow window)
        {
            DataTable s = new DataTable();
            //如果在查询下
            if (window.Select.IsSelected == true)
            {
                        //算出大范围
                        int bTopNumber = Bridge.SPageNumber;
                        //算出小范围
                        int lTopNumber = Bridge.SPageNumber-22;
                        string sqlcommand1 = String.Format("select top {0} * from  Goods where Goods_Id  not in(select top {1} Goods_Id from Goods)", bTopNumber, lTopNumber);
                        s = DataAccess.GetResult(sqlcommand1);                                  
            }
            //如果在入库下
            if (window.StockIn.IsSelected == true)
            {               
                        //算出大范围
                        int bTopNumber = Bridge.IPageNumber;
                //算出小范围
                         int lTopNumber = Bridge.IPageNumber- 22;
                        string sqlcommand1 = String.Format("select top {0} * from  Goods where Goods_Id  not in(select top {1} Goods_Id from Goods)", bTopNumber, lTopNumber);
                       s = DataAccess.GetResult(sqlcommand1);                                 
            }
            //如果在出库下
            if (window.StockOut.IsSelected == true)
            {    
                        //算出大范围
                        int bTopNumber = Bridge.OPageNumber; 
                        //算出小范围
                        int lTopNumber = Bridge.OPageNumber-22;
                        string sqlcommand1 = String.Format("select top {0} * from  Goods where Goods_Id  not in(select top {1} Goods_Id from Goods)", bTopNumber, lTopNumber);
                        s = DataAccess.GetResult(sqlcommand1);                               
            }
            try
            {
                s.Columns[0].ColumnName = "货物编码";
                s.Columns[1].ColumnName = "货物名";
                s.Columns[2].ColumnName = "仓库号";
                s.Columns[3].ColumnName = "库存";
                s.Columns[4].ColumnName = "生产日期";
                s.Columns[5].ColumnName = "保质期";
            }
            catch (Exception e)
            {
                string sqlcommand1 = String.Format("select top 22 * from  Goods where Goods_Id  not in(select top {0} Goods_Id from Goods)", Bridge.SPageNumber - 22);
                s = DataAccess.GetResult(sqlcommand1);
                s.Columns[0].ColumnName = "货物编码";
                s.Columns[1].ColumnName = "货物名";
                s.Columns[2].ColumnName = "仓库号";
                s.Columns[3].ColumnName = "库存";
                s.Columns[4].ColumnName = "生产日期";
                s.Columns[5].ColumnName = "保质期";
                Console.WriteLine(e.Message);
            }
            return s;
        }
    }
    //发货记录管理
    class DeliverRecord
    {
        //显示首页 查询发货记录并修改列名并显示在datagrid上  刷新在执行方法就行
        public static void DeliverSelect(MainWindow window)
        {
            string sqlcommand = String.Format("select * from DeliverRecord");
            DataTable s = DataAccess.GetResult(sqlcommand);
            s.Columns[0].ColumnName = "订单编号";
            s.Columns[1].ColumnName = "发货时间";
            s.Columns[2].ColumnName = "客户编号";
            s.Columns[3].ColumnName = "客户姓名";
            s.Columns[4].ColumnName = "客户联系方式";
            s.Columns[5].ColumnName = "发货地";
            s.Columns[6].ColumnName = "目的地";
            s.Columns[7].ColumnName = "货物";
            s.Columns[8].ColumnName = "数量";
            window.SDataShow3.ItemsSource = s.DefaultView;
            window.IDataShow3.ItemsSource = s.DefaultView;
            window.UDataShow3.ItemsSource = s.DefaultView;
        }
        //发货记录准确查询
        public static DataTable Deliver_SelectExactly(MainWindow window)
        {
            DataTable s = new DataTable();
            string sqlcommand;
            //订单编号存在
            if (window.Deliver_SOrder_Id.Text != "")
            {
                sqlcommand = String.Format("select * from DeliverRecord where Order_Id='{0}'", window.Deliver_SOrder_Id.Text);
            }
            else
            {
                sqlcommand = String.Format("select * from DeliverRecord where Customer_Id='{0}'", window.Deliver_SCustomer_Id.Text);
            }
            s = DataAccess.GetResult(sqlcommand);
            s.Columns[0].ColumnName = "订单编号";
            s.Columns[1].ColumnName = "订单创建时间";
            s.Columns[2].ColumnName = "客户编号";
            s.Columns[3].ColumnName = "客户姓名";
            s.Columns[4].ColumnName = "客户联系方式";
            s.Columns[5].ColumnName = "发货地";
            s.Columns[6].ColumnName = "目的地";
            s.Columns[7].ColumnName = "货物";
            s.Columns[8].ColumnName = "数量";
            return s;
        }
        //发货记录条件查询
        public static DataTable Deliver_SelectByCondition(MainWindow window)
        {
            DataTable s = new DataTable();
            string place_Of_Delivery = window.Deliver_Place_Of_Delivery.Text;
            string destination = window.Deliver_Destination.Text;
            string goods = window.Deliver_Goods.Text;
            string deliver_DateTime_Start = window.Deliver_DateTime_Start.Text;
            string deliver_DateTime_End = window.Deliver_DateTime_End.Text;
            //1=1永真条件只是让后面动态加语法成立
            string sqlcommand = String.Format("select * from DeliverRecord where 1=1");
            //至少有一个条件
            if (place_Of_Delivery != "" || destination != "" || goods != "" ||(deliver_DateTime_Start != "" && deliver_DateTime_End != ""))
            {

                if (place_Of_Delivery != "")
                {
                    string place_Of_Delivery_Condition = String.Format("and Place_Of_Delivery='{0}'", place_Of_Delivery);
                    sqlcommand += place_Of_Delivery_Condition;

                }
                if (destination != "")
                {
                    string destination_Condition = String.Format("and Destination='{0}'", destination);
                    sqlcommand += destination_Condition;
                }
                if (goods != "")
                {
                    string goods_Condition = String.Format("and Goods='{0}'", goods);
                    sqlcommand += goods_Condition;
                }
                if (deliver_DateTime_Start != "" && deliver_DateTime_End != "")
                {
                    string create_DateTime_Condition = String.Format(" and Deliver_DateTime>='{0}' and Deliver_DateTime<='{1}'", deliver_DateTime_Start, deliver_DateTime_End);
                    sqlcommand += create_DateTime_Condition;
                }
            }
            else
            {
                MessageBox.Show("请至少输入一对数据");
            }
            s = DataAccess.GetResult(sqlcommand);
            s.Columns[0].ColumnName = "订单编号";
            s.Columns[1].ColumnName = "创建时间";
            s.Columns[2].ColumnName = "客户编号";
            s.Columns[3].ColumnName = "客户名";
            s.Columns[4].ColumnName = "客户联系方式";
            s.Columns[5].ColumnName = "发货地";
            s.Columns[6].ColumnName = "目的地";
            s.Columns[7].ColumnName = "货物";
            s.Columns[8].ColumnName = "数量";
            return s;
        }
        //发货记录插入
        public static DataTable Deliver_Insert(MainWindow window)
        {
            DataTable s = new DataTable();
            string iOrder_Id = window.Deliver_IOrder_Id.Text;
            string iDeliver_DateTime = window.IDeliver_DateTime.Text;
            string iCustomer_Id = window.Deliver_ICustomer_Id.Text;
            string iCustomer_Name = window.Deliver_ICustomer_Name.Text;
            string iCustomer_Tel = window.Deliver_ICustomer_Tel.Text;
            string iPlace_Of_Delivery = window.Deliver_IPlace_Of_Delivery.Text;
            string iDestination = window.Deliver_IDestination.Text;
            string iGoods = window.Deliver_IGoods.Text;
            string iNumber = window.Deliver_INumber.Text;

            string sqlcommand1 = String.Format("select count(*) from DeliverRecord where Order_Id='{0}'", iOrder_Id);
            string sqlcommand;
            //判断电话合法性 就这里做了调用 其它太多了不想做
            if(VerifyLegitimacy.VerifyMolilePhone(iCustomer_Tel)|| VerifyLegitimacy.VerifyTelNumber(iCustomer_Tel))
            {
                //判断数量数据合法性
                if (VerifyLegitimacy.VerifyPositiveOrNegativeOrZero(iNumber))
                {
                    //表里存在记录
                    if (DataAccess.ExecuteScalar(sqlcommand1) > 0)
                    {
                        MessageBox.Show("存在此条记录，请勿重复插入");
                    }
                    else
                    {
                        sqlcommand = String.Format("insert into DeliverRecord values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}',{8})", iOrder_Id, iDeliver_DateTime, iCustomer_Id, iCustomer_Name, iCustomer_Tel, iPlace_Of_Delivery, iDestination, iGoods, iNumber);
                        s = DataAccess.GetResult(sqlcommand);
                    }
    
                }
                else
                {
                    MessageBox.Show("数量不能为负");
                }
            }
            else
            {
                MessageBox.Show("电话有勿，请重新输入");
            }
            return s;
        }
        //发货记录修改
        public static DataTable Deliver_Update(MainWindow window)
        {
            DataTable s = new DataTable();
            string uOrder_Id = window.Deliver_UOrder_Id.Text;
            string uDeliver_DateTime = window.UDeliver_DateTime.Text;
            string uCustomer_Id = window.Deliver_UCustomer_Id.Text;
            string uCustomer_Name = window.Deliver_UCustomer_Name.Text;
            string uCustomer_Tel = window.Deliver_UCustomer_Tel.Text;
            string uPlace_Of_Delivery = window.Deliver_UPlace_Of_Delivery.Text;
            string uDestination = window.Deliver_UDestination.Text;
            string uGoods = window.Deliver_UGoods.Text;
            string uNumber = window.Deliver_UNumber.Text;
            string sqlcommand1 = String.Format("select count(*) from DeliverRecord where Order_Id='{0}'", uOrder_Id);
            string sqlcommand;
            //表里存在记录
            if (DataAccess.ExecuteScalar(sqlcommand1) > 0)
            {
                sqlcommand = String.Format("update DeliverRecord set Deliver_DateTime='{0}',Customer_Id='{1}',Customer_Name='{2}',Customer_Tel='{3}',Place_Of_Delivery='{4}',Destination='{5}',Goods='{6}',Number={7} where Order_Id='{8}'", uDeliver_DateTime, uCustomer_Id, uCustomer_Name, uCustomer_Tel, uPlace_Of_Delivery, uDestination, uGoods, uNumber,uOrder_Id);
                s = DataAccess.GetResult(sqlcommand);
            }
            else
            {
                MessageBox.Show("记录中不存在此记录");
            }
            return s;
        }
    }
    //配置文件访问类
    class ConfigAccess
    {
        //读AppSettings
        // string strPath = ConfigurationManager.AppSettings["Path"];
        public static string GetAppConfig(string strKey)
        {
            foreach (string key in ConfigurationManager.AppSettings)
            {
                if (key == strKey)
                {
                    return ConfigurationManager.AppSettings[strKey];
                }
            }
            return null;
        }
        //写AppSettings
        ///<summary>
        ///在＊.exe.config文件中appSettings配置节增加一对键、值对
        ///</summary>
        ///<param name="newKey"></param>
        ///<param name="newValue"></param>
        public static void UpdateAppConfig(string newKey, string newValue)
        {
            bool isModified = false;
            foreach (string key in ConfigurationManager.AppSettings)
            {
                if (key == newKey)
                {
                    isModified = true;
                }
            }

            // Open App.Config of executable
            Configuration config =
                ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
            // You need to remove the old settings object before you can replace it
            if (isModified)
            {
                config.AppSettings.Settings.Remove(newKey);
            }
            // Add an Application Setting.
            config.AppSettings.Settings.Add(newKey, newValue);
            // Save the changes in App.config file.
            config.Save(ConfigurationSaveMode.Modified);
            // Force a reload of a changed section.
            ConfigurationManager.RefreshSection("appSettings");
        }

    }
    //Md5加密
    class Md5
    {
        //调用自带的CompurtHash（）编码完在返回
        public static string CreateMd5(string str)
        {
            MD5 md = MD5.Create();
            byte[] st = md.ComputeHash(Encoding.UTF8.GetBytes(str));
            string s = "";
            for (int i = 0; i < st.Length; i++)
                s += st[i].ToString("x");
            return s;

        }
    }
    //共享类
    class Bridge
    {
        public static string ID;
        public static string Position;
        public static string Name;
        //库存视图当前记录数
        public static int SPageNumber = 22;
        public static int IPageNumber = 22;
        public static int OPageNumber = 22;
        //计算出货物视图的总页数
        static string sqlcommand = String.Format("select count(*) from Goods");
       public static int temp = DataAccess.ExecuteScalar(sqlcommand);
        public static int PageCount = temp%22==0? temp / 22: temp / 22+1;

        //数据统计分析结果
        public static string AnalysisResult;

        #region 正在写没调试
        //通用当前记录数   有几个就写几个 这里这测试写一个viewName 倒是后viewName改成对应视图名 上一页下一页用
        public static int viewNamePageNumber = 22;
        /// <summary>
        /// 获取子控件  没用无法动态获取name
        /// </summary>
        /// <typeparam name="T">子控件类型</typeparam>
        /// <param name="obj">父控e799bee5baa6e997aee7ad94e58685e5aeb931333337383230件</param>
        /// <param name="name">名称</param>
        /// <returns></returns>
        public static T GetChildObject<T>(DependencyObject obj, string name) where T : FrameworkElement
        {
            DependencyObject child = null;
            T grandChild = null;
            for (int i = 0; i <= VisualTreeHelper.GetChildrenCount(obj) - 1; i++)
            {
                child = VisualTreeHelper.GetChild(obj, i);


                if (child is T && (((T)child).Name == name && !string.IsNullOrEmpty(name)))
                {
                    return (T)child;
                }
                else
                {
                    grandChild = GetChildObject<T>(child, name);
                    if (grandChild != null)
                        return grandChild;
                }
            }
            return null;
        }

        /// <summary>
        /// //通用跳页 只是获得数据 应该在调用时对表的列名进行对应的修改并把对应的当前记录数修改
        /// </summary>
        /// <param name="tableName">数据库的表名</param>
        /// <param name="key">表额主键名</param>
        /// <param name="textBox">试图的页数输入框</param>
        /// <returns></returns>
        public static DataTable UsuallyRefreshSkipPage(string tableName, string key,TextBox textBox)
        {
            DataTable s = new DataTable();
            //算出大范围
            int bTopNumber = int.Parse(textBox.Text) * 22; //int.Parse(window.DeliverRecord_IPageNumber.Text) * 22;
            //算出小范围
            int lTopNumber = (int.Parse(textBox.Text) - 1) * 22; ;//(int.Parse(window.DeliverRecord_IPageNumber.Text) - 1) * 22; ;
            string sqlcommand1 = String.Format("select top ({0}) * from  {1} where {2} not in(select top ({3}) {2} from {1} )", bTopNumber, tableName, key, lTopNumber);
           // MessageBox.Show(sqlcommand1);
            s = DataAccess.GetResult(sqlcommand1);
            return s;
        }
        //通用PageCount结果数组
        public static int[] usuallyPageCount =new int[5];//不实例化或者不给初值会报错；
        //通用视图总页数计算 在构造里执行一次并把结果传给usuallyPageCount然后对应的试图使用对应的元素
        public static int[] UsuallyPageCount() {
            int[] temp=null;
            //有几个就写几个查询然后都赋值给temp
            int temp1 = DataAccess.ExecuteScalar("select count(*) from DeliverRecord");
            usuallyPageCount[0] = temp1 / 22 + 1;
            return temp;
    }
        //
        #endregion
    }
    //数据库操作类 加了try-catch块 并修改部分返回值位置或者值
    class DataAccess
    {
        //DESKTOP-LGDC4H8
        //public static string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        public static string constr = "Data Source=.;Initial Catalog=ELM;Integrated Security=True";//自己电脑
        //查询并把结果用datatable return 似乎只有select 才返回数据 其他好像只返回受影响行数或者count
        public static DataTable GetResult(string sql)
        {
            DataTable dataTable = new DataTable();
            try {
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sql, constr);
                sqlDataAdapter.Fill(dataTable);
                Log.LogWrite("执行sql语句:"+sql+" 并返回数据");
                return dataTable;
            }
            catch (Exception e){
                MessageBox.Show(e.ToString());
                return dataTable;
            }
        }
        //只执行  改过不知道能不能用了
        public static void NoResult(string sql)
        {
            try
            {
                SqlConnection con = new SqlConnection(constr);
                SqlCommand com = con.CreateCommand();
                com.CommandText = String.Format(sql);
                con.Open();
                com.ExecuteReader();
                con.Close();
                Console.WriteLine("catch里可以加这段代码");
            }
            catch (Exception e)
            {
                MessageBox.Show(e.ToString());
            }
        }
        //查询受影响行数
        public static int ExcuteNonQuery(string sql)
        {
            int h=10086;
            try
            {
                using (SqlConnection con = new SqlConnection(constr))
                {

                    using (SqlCommand com = new SqlCommand(sql))
                    {
                        com.Connection = con;
                        con.Open();
                        //con.Close();//位置原来在最下方
                        h= com.ExecuteNonQuery();
                        return h;
                    }
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.ToString());
                return h;
            }
        }
        //使用于select语句
        public static int ExecuteScalar(string sql)
        {
            object o=null;
            try
            {
                using (SqlConnection con = new SqlConnection(constr))
                {
                    using (SqlCommand com = con.CreateCommand())
                    {
                        com.CommandText = sql;
                        con.Open();
                        o = com.ExecuteScalar();
                       // con.Close();//位置原来在最下方
                        return Convert.ToInt32(o);
                    }
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.ToString());
                return Convert.ToInt32(o);
            }

        }
        //事务执行:新增还没用过
        public bool TransactionExce(string[] sqls)
        {
            SqlConnection con = new SqlConnection(constr);
            try
            {
                con.Open();
                SqlTransaction tran = con.BeginTransaction();
                SqlCommand com = con.CreateCommand();
                try
                {
                    com.Transaction = tran;
                    com.CommandType = CommandType.Text;
                    for (int i = 0; i < sqls.Length; i++)
                    {
                        com.CommandText = sqls.ToString();
                        com.ExecuteNonQuery();
                    }
                    tran.Commit();
                    return true;
                }
                catch
                {
                    try
                    {
                        tran.Rollback();
                        return false;
                    }
                    catch
                    {
                        return false;
                    }
                }
            }
            catch
            {
                return false;
            }
            finally
            {
                con.Close();
            }
        }

    }
    //日志类
    class Log
    {
        //日志写入  只有 getresult调用了  因为已经包含绝大多
        public static void LogWrite(string message)
        {
            try
            {
                string logFileName = @"C:\Users\黄燕槟\Desktop\ELM\ELM\Logs\ELMLog" + DateTime.Now.ToString("yyyy -MM-dd") + ".log";
                using (TextWriter logFile = TextWriter.Synchronized(File.AppendText(logFileName)))
                {
                    logFile.WriteLine("\r\n");
                    logFile.WriteLine(DateTime.Now);
                    logFile.WriteLine(message);
                    logFile.Flush();
                    logFile.Close();
                }

            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }
    }
    //数据合法性验证类 做了还没具体调用
    class VerifyLegitimacy
    {
        //电话验证 正确返回true错误返回false
        public static bool VerifyTelNumber(string data)
        {
            return Regex.IsMatch(data, @"^(\d{3,4}-)?\d{6,8}$");
        }
        //手机验证 正确返回true错误返回false
        public static bool VerifyMolilePhone(string data)
        {
            if (data.Length < 11)
            {
                return false;
            }
            //电信手机号码正则
            string dianxin = @"^1[3578][01379]\d{8}$";
            Regex regexDX = new Regex(dianxin);
            //联通手机号码正则
            string liantong = @"^1[34578][01256]\d{8}";
            Regex regexLT = new Regex(liantong);
            //移动手机号码正则
            string yidong = @"^(1[012345678]\d{8}|1[345678][012356789]\d{8})$";
            Regex regexYD = new Regex(yidong);
            if (regexDX.IsMatch(data) || regexLT.IsMatch(data) || regexYD.IsMatch(data))
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        //正负验证 负数返回false正数或0返回true
        public static bool VerifyPositiveOrNegativeOrZero(string data)
        {
            int number = int.Parse(data);
            //为负数
            if (-number > 0)
            {
                return false;
            }
            else//正数或0
            {
                return true;
            }
        }
    }
    //客户管理的客户分析类
    class CustomerAnalysis
    {/*
        最近一单距离现在几天 忠诚值  7天达到满分不然就按占比
        最大单多少钱  信赖值=钱数  5w满分 不然就按占比
        单与单之间的平均间隔 依赖值 1礼拜一次达到满分 不然就按占比
        单子数量  需求值   100单发达满分不然就按占比

        购买力=四值相加

        99分 单子数量占35% 间隔占35% 最大单占15% 最近占15%
         */

        //根据客户号计算忠诚度
        public static double GetLoyalById(string id )
        {
            //初始分-1是为了避免罗素悖论（理发师悖论）
            double initialScore = 99 * 0.15;        
            string sql = String.Format("select top 1 FLOOR(DATEDIFF(MINUTE, Create_DateTime, getdate()) / 1440) from FormalOrder where Create_DateTime<getdate() and Customer_Id='{0}' order by Create_DateTime ASC", id);
            DataTable s = DataAccess.GetResult(sql);
            double proportion;
            if (int.Parse(s.Rows[0][0].ToString()) >= 7)
            {
                proportion = 1;
            }
            else
            {
                proportion = int.Parse(s.Rows[0][0].ToString()) / 7d; ;
            }
            double loyal = proportion * initialScore;
            return loyal;
        }
        //根据客户号计算信赖度
        public static double GetTrustById(string id)
        {
            double initialScore = 99 * 0.15;
            string sql = String.Format("select max(PaymentAmount) from TransactionRecord where Pay_State='已支付' and Formal_Order_Id in(select Order_Id Formal_Order_Id from FormalOrder where Customer_Id='{0}')", id);
            DataTable s = DataAccess.GetResult(sql);
            double proportion;
            if (Convert.ToDouble(s.Rows[0][0])>=50000)
            {
                proportion = 1;
            }
            else
            {
                proportion = Convert.ToDouble(s.Rows[0][0].ToString()) / 50000d;
            }

            double trust = proportion * initialScore;
            return trust;
        }
        //根据客户号计算依赖度
        public static double GetDependentById(string id)
        {
            double initialScore = 99 * 0.35;
            string sql = String.Format("select Create_DateTime from FormalOrder where Customer_Id='{0}' order by Create_DateTime ASC", id);//ASC DESC
            DataTable s = DataAccess.GetResult(sql);
            int countDay = 0;
            string[] dateData = new string[s.Rows.Count];
            for(int i=0;i<s.Rows.Count;i++)
            {
                dateData[i] = s.Rows[i][0].ToString();
            }
            for (int i=0;i< dateData.Length-1;i++)
            {
                //较大日期
                string bigDate = s.Rows[i+1][0].ToString();
                //较小日期
                string smallDate = s.Rows[i][0].ToString();
                DateTime start = Convert.ToDateTime(Convert.ToDateTime(smallDate).ToShortDateString());
                DateTime end = Convert.ToDateTime(Convert.ToDateTime(bigDate).ToShortDateString());
                TimeSpan sp = end.Subtract(start);
                int days = sp.Days;
                countDay += days;
            }
            double proportion;
            if (countDay/ s.Rows.Count <= 7)
            {
                proportion = 1;
            }
            else
            {
                proportion= 7d/countDay/s.Rows.Count ; 
            }
            double dependent = proportion * initialScore;
            return dependent;
        }
        //根据客户号计算需求值
        public static double GetDemandById(string id)
        {
            double initialScore = 99 * 0.35;
            string sql = String.Format("select count(Order_Id) from FormalOrder where Customer_Id='{0}' and Order_State='交易完成'", id);
            int orderCount = DataAccess.ExecuteScalar(sql);
            double proportion;
            if (orderCount >= 100)
            {
                proportion = 1;
            }
            else
            {
                proportion = orderCount/ 100d; ;
            }
            double demand = proportion * initialScore;
            return demand;
        }
    }
}